Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Manipulating rows in the browse

This section describes different ways you can change browse rows.

Refreshing browse rows

If you use a read-only browse and a user updates a record in some way outside the browse, you need a way to refresh the currently focused browse row with the new data. Use the following statement form at the end of the update code to refresh the browse:

DISPLAY column-name ... WITH BROWSE browse-name.

Repositioning focus

The browse and its query must remain in sync. At times, you might have to do some behind-the-scenes manipulation of the result set to keep them in sync. Generally speaking, whenever the user repositions the browse by selecting a new row, its query repositions automatically to that same row. Likewise, if the query is programmatically repositioned to a different row, the browse automatically coordinates with it and the new current row within the query becomes the currently selected row in the browse. However, if you simply FIND a row using the query’s buffer, this does not reposition either the query or its browse. This is why you might need to resync the query in your code in order to properly refresh the browse. To do this, you can use the REPOSITION statement. The REPOSITION statement moves the database cursor to the specified position and adjusts the browse viewport to display the new row.

To avoid display flashing when doing programmatic repositions, you can set the REFRESHABLE browse attribute to FALSE, do the REPOSITION, and then set REFRESHABLE to TRUE. This suspends any redisplay of the browse until after the operation is complete.

In addition, the SET-REPOSITIONED-ROW( ) method gives you control over the position in the viewport where the browse displays the repositioned row. The method takes two arguments:

Its first Integer argument tells Progress which row (that is within the browse viewport) to position to. For example, if your browse displays seven rows at a time, you could use the SET-REPOSITIONED-ROW method to show a newly positioned row in the middle of the viewport by using an argument value of 4.
The second Character argument to the method can be ALWAYS or CONDITIONAL. If you specify ALWAYS, the browse is always adjusted to show the repositioned row in the specified position. If you specify CONDITIONAL, then the browse adjusts only if the repositioned row is not already in the viewport.

Note that normally you set SET-REPOSITIONED-ROW( ) once for the session for a browse to establish its behavior wherever it is used. You can also use the GET-REPOSITIONED-ROW() method to return as an Integer the current target viewport row for repositions.

To reposition the query and the browse along with it:

Define a new fill-in called iOrder of INTEGER data type.
Define this LEAVE trigger for the fill-in field:

DO:   BROWSE OrderBrowse:SET-REPOSITIONED-ROW(3, "CONDITIONAL").   ASSIGN iOrder.   FIND Order WHERE order.orderNum = iOrder NO-ERROR.   IF AVAILABLE (Order) THEN       REPOSITION OrderBrowse TO ROWID ROWID(Order) NO-ERROR. END.

When you enter a value into the field and press TAB, the query and the browse are both repositioned to that row. If you enter a row that Progress can’t find or that isn’t an Order for the current Customer, Progress suppresses these errors and nothing changes in the browse. The SET-REPOSITIONED-ROW method makes the new row the third row in the viewport unless it’s already displayed.

For a multiple-select browse, the concepts of focus and selection separate. Selection indicates that the user has selected a record. The user can select many records. The last selected record is the one in the record buffer. Focus indicates that the record has input focus. There can be only one focused row, and it might or might not be a selected row. In a single-select browse, selection and focus are one and the same.

Updating browse rows

By default, Progress handles the process of updating data in the records managed by an updateable browse’s query. Because you should normally create applications that do not have a client-side dependency on a direct database connection, it is not advisable for you to define browses directly against database tables or to update database tables directly through a browse. Therefore, the default update behavior is described here to make you aware of what Progress does when you are browsing a database table, not to recommend that you take advantage of it. Some of these steps might not be completely clear to you until you read Chapter 17, "Managing Transactions," but it gives you an overview of the steps.

Assuming that the browse starts with the record in NO-LOCK state, Progress follows these steps:

On any user action that modifies data in an editable browse cell, Progress again gets the record with a SHARE-LOCK, which means that no other user can change the record. If the data has changed, Progress warns the user and redisplays the row with the new data.
When the user leaves the row and has made changes to the row, Progress starts a transaction (or subtransaction) and gets the record from the database with EXCLUSIVE-LOCK NO-WAIT, which means that no other user can lock the record in any way. If no changes were made, Progress does not start a transaction.
If the GET with EXCLUSIVE-LOCK is successful, Progress updates the record, disconnects it (removes the lock), ends the transaction, and downgrades the lock to its original status.
If the GET with EXCLUSIVE-LOCK fails, Progress backs out the transaction, displays an error message, keeps the focus on the edited row, and retains the edited data.

You also have the option to disable this default behavior and programmatically commit the changes by way of a trigger on the ROW-LEAVE event. To do this, you must supply the NO-ASSIGN option in the DEFINE BROWSE statement.

If you define your browses against temp-tables, as you are doing in the OlineBrowse of the test procedure, then there are no record locks and no possibility of contention with other users for the records in the temp-table. Therefore, you can let Progress update the temp-table by defining the browse without the NO-ASSIGN keyword and then take care of updating the database from the temp-table yourself. You learn how to do this in Chapter 20, " Creating and Using Dynamic Temp-tables and Browses."

Creating browse rows

In an updateable browser, you might want to allow the user to add new records to the underlying temp-table or database table. Programmatically, this requires three separate steps:

Create a blank line in the browse viewport with the INSERT-ROW() method and populate it with new data. INSERT-ROW takes a single optional argument, which is the string “BEFORE” or “AFTER”. This argument tells Progress whether to insert the new row before or after the currently selected row in the browse. The default is “BEFORE”. You can use the INSERT-ROW() browse method in an empty browse. It places a new row at the top of the viewport.
Use the CREATE statement and ASSIGN statement to update the database or the underlying temp-table.
Add a reference to the result list with the CREATE-RESULT-LIST-ENTRY( ) method. This is the list of record identifiers that Progress uses to keep track of the set of rows in the query. This step is only necessary if you do not plan to reopen the query after the update, because reopening the query completely refreshes the list. However, this method makes reopening the query unnecessary for most applications.

All three steps are required to create the record and keep the database, query, and browse in sync. Also, there are several possible side effects to allowing the user to add a record through an updateable browse. They include placing new records out of order and adding records that do not match the query. To eliminate these side effects, you can reopen the query after each new record is added.

To add a button to the test window that lets you add new OrderLines to the temp-table through the OlineBrowse:

In the Definitions section of the h-CustOrderWin5.w procedure, beneath the browse define for OlineBrowse, define a handle variable called hBrowse. You will use this variable to hold the handle of the OrderLine browse because it is needed in several places.
Define a new buffer for the OrderLine temp-table called ttOline2. You’ll use this buffer to hold onto the values in the current temp-table record as you create a new record, and to copy some of the values from the old record to the new one.
Assign the OlineBrowse handle to the new hBrowse variable:

DEFINE VARIABLE hBrowse AS HANDLE     NO-UNDO. DEFINE BUFFER ttOline2 FOR ttOline. hBrowse = BROWSE OlineBrowse:HANDLE.

Drop a new button onto the window where it will not be overwritten by the OrderLine browse when it is displayed.
Name the new button btnNew and give it a label of New OrderLine.
Define a CHOOSE trigger for the button:

DO:     IF hBrowse:NUM-SELECTED-ROWS = 0 THEN     DO:        APPLY "END" TO hBrowse.        hBrowse:SELECT-FOCUSED-ROW().     END.     hBrowse:INSERT-ROW("AFTER"). END.

This trigger code uses the handle of the OlineBrowse that you just assigned up in the Definitions section and inserts a new row in the browse after the currently selected row. First, it checks to make sure that there is a selected row using the browse’s NUM-SELECTED-ROWS attribute. If the value of this attribute is zero, then the user has not selected any row. In this case, the trigger positions to the end of the browse by applying the END event to it and then selects that row using the SELECT-FOCUSED-ROW method.

Back in the procedure’s Definitions section, add this ROW-LEAVE trigger block for the OlineBrowse following the statement that assigns its handle to hBrowse:

ON "ROW-LEAVE" OF BROWSE OlineBrowse DO:     IF hBrowse:NEW-ROW THEN     DO:         FIND LAST ttOline2.         CREATE ttOline.         BUFFER-COPY ttOline2 TO ttOline             ASSIGN ttOline.LineNum = ttOline2.LineNum + 1.         ASSIGN INPUT BROWSE OlineBrowse ttOline.Qty                 ttOline.Price ttOline.Discount.         DISPLAY ttOline.OrderNum ttOline.LineNum                 ttOline.ItemNum ttOline.ItemName                 WITH BROWSE OlineBrowse.         hBrowse:CREATE-RESULT-LIST-ENTRY().     END. END.

This code first checks the NEW-ROW attribute of the browse to see if the row the user left is one newly created by the New OrderLine button.

Because you’re not prepared to do a full-fledged database update from the browse yet, the example simply creates a new row in the temp-table. To simplify the example, you start by copying the fields from the previous record into the new one. To do this, you FIND the LAST record in the temp-table using the second buffer ttOline2. This gives you the highest LineNum value so that you can increment it for the new record. Note that this means that other initial field values are also assigned from the last record regardless of where in the viewport the user inserted the new record.

You create a new temp-table record and BUFFER-COPY the last record into the new one, incrementing the LineNum field to give it a distinct value. This is important because the temp-table has the same unique index on the OrderNum and LineNum fields as the underlying OrderLine table in the database, so changing the LineNum has to happen in the BUFFER-COPY statement to avoid a unique index violation.

Next, the code assigns values to those columns that are enabled for input. The INPUT keyword on the ASSIGN statement tells Progress to take the values from the screen buffer for each column. Then the new values are displayed in the browse.

Finally, you create a result list entry for the new record. If you didn’t create a result list entry the row would be lost if you were to scroll it out of the browse’s viewport. Reopening the query would also rebuild the result list, and also assure that all rows are in the proper order, so you can use either technique for getting the browse and the query back in sync.

Run the window. Now you can add a record to the OrderLine browse:



Once again, you’ll learn how to get new records into the database in Chapter 16, " Updating Your Database and Writing Triggers."

Deleting browse rows

Deleting a record by way of a browse is a two-step process. First, you need to delete the record from the underlying temp-table or database table, and then you need to remove the record from the browse itself, along with the query’s result list. If you are browsing a database table directly and the user indicates a deletion, you should again get the records by EXCLUSIVE-LOCK NO-WAIT and then use the DELETE statement to remove the records from the database. In the case of a temp-table, you can simply use the DELETE statement to remove the records.

Next, you use the DELETE-SELECTED-ROWS( ) method to delete one or more selected records from both the browse widget and the associated query result list.

To add a Delete button to the test window and use it to remove rows from the OlineBrowse and its temp-table:

Drop another button onto the window called btnDelete and give it a label of Delete OrderLines.
Write this CHOOSE trigger for the new button:

DO:   DEFINE VARIABLE iRow AS INTEGER NO-UNDO.   DO iRow = 1 TO hBrowse:NUM-SELECTED-ROWS:      hBrowse:FETCH-SELECTED-ROW(iRow).      DELETE ttOline.   END.   hBrowse:DELETE-SELECTED-ROWS(). END.

Because the OlineBrowse is a multiple-selection browse, you can use it to delete one or more rows at once. This code walks through the set of selected rows and retrieves each one in turn using the FETCH-SELECTED-ROW method. This method repositions the temp-table query to that row, so that it can be deleted. The code then uses the DELETE-SELECTED-ROWS method to delete all the rows from the browse itself, along with the query’s result list entries for them.

If you run the test window, you can now delete one or more rows from the list of OrderLines for an Order. Remember that these records are deleted only from the temp-table. You would have to make a call to a procedure connected to the database to pass the list of rows to delete from the database table.